package model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;

import controller.BanqueProxy;
import bdd.DatabaseQuery;

/**
 * Classe regroupant les methodes de gestion des ventes et des reservations 
 * 
 * @author Equipe A
 */
public class Vente {

	/*
	 * CONSTRUCTEURS
	 */
	/**
	 * Constructeur par defaut
	 */
	public Vente() {
	}


	/*
	 * METHODES
	 */
	/**
	 * Permet de recuperer tous les articles correspondant a
	 * une liste de criteres de recherche
	 * 
	 * @param article
	 *                   La liste des criteres de recherche
	 * @return List<Article>
	 *                   La liste des articles repondant aux criteres
	 * @throws SQLException
	 *                   Exception SQL levee
	 */
	public List<Article> trouverArticle(Article article) throws SQLException {

		List<Article> listeArticle = new ArrayList<Article>();
		DatabaseQuery data = new DatabaseQuery();
		Connection conn = data.getDBConnection();
		Article articleTrouve = new Article();
		Statement stmt = data.getNewStatement();
		ResultSet rset = stmt.getResultSet();

	    if (article.getCode() != 0) {
	    	stmt.execute("SELECT code, nom, description, prix FROM Article WHERE code = '" + article.getCode() + "';");
	    	rset = stmt.getResultSet();
	    	do {
	    		articleTrouve.setCode(rset.getInt(1));
	    		articleTrouve.setNom(rset.getString(2));
	    		articleTrouve.setDescription(rset.getString(3));
	    		articleTrouve.setPrix(rset.getFloat(4));
	    		listeArticle.add(articleTrouve);
	    	} while (rset.next());
	    }
	    if (article.getNom() != null) {
	    	stmt.execute("SELECT code, nom, description, prix FROM Article WHERE nom = '" + article.getNom() + "';");
	    	rset = stmt.getResultSet();
	    	do {
	    		articleTrouve.setCode(rset.getInt(1));
	    		articleTrouve.setNom(rset.getString(2));
	    		articleTrouve.setDescription(rset.getString(3));
	    		articleTrouve.setPrix(rset.getFloat(4));
	    		listeArticle.add(articleTrouve);
	    	} while (rset.next());
	    }
	    if (article.getDescription() != null) {
	    	stmt.execute("SELECT code, nom, description, prix FROM Article WHERE description = '" + article.getDescription() + "';");
	    	rset = stmt.getResultSet();
	    	do {
	    		articleTrouve.setCode(rset.getInt(1));
	    		articleTrouve.setNom(rset.getString(2));
	    		articleTrouve.setDescription(rset.getString(3));
	    		articleTrouve.setPrix(rset.getFloat(4));
	    		listeArticle.add(articleTrouve);
	    	} while (rset.next());
	    }
	    if (article.getPrix() != 0) {
	    	stmt.execute("SELECT code, nom, description, prix FROM Article WHERE prix = " + article.getPrix() + ";");
	    	rset = stmt.getResultSet();
	    	do {
	    		articleTrouve.setCode(rset.getInt(1));
	    		articleTrouve.setNom(rset.getString(2));
	    		articleTrouve.setDescription(rset.getString(3));
	    		articleTrouve.setPrix(rset.getFloat(4));
	    		listeArticle.add(articleTrouve);
	    	} while (rset.next());
	    }

	    DatabaseQuery.free(rset, stmt, conn);
		return listeArticle;
	}

	/**
	 * 
	 * @param idCompteRetrait
	 *                   Compte duquel on debite de l'argent
	 * @param valeur
	 *                   Le montant a debiter du compte d'id idCompteRetrait
	 * @param idCompteAjout
	 *                   Compte sur lequel on ajoute l'argent debite
	 * @param motDePasseCompteRetrait
	 *                   Code de verification pour acceder au compte "retrait"
	 * @return String
	 *                   Notification du bon deroulement ou non du paiement
	 * @throws ServletException
	 *                   Exception Servlet levee
	 * @throws IOException
	 *                   Exception IO levee
	 */
	public String payerArticle(String idCompteRetrait, int valeur, String idCompteAjout, String motDePasseCompteRetrait) throws ServletException, IOException {
		BanqueProxy bp = new BanqueProxy();
		String articlePaye = bp.effectuerPaiement(idCompteRetrait, valeur, idCompteAjout, motDePasseCompteRetrait);
		return articlePaye;
	}

	/**
	 * Enregistre une reservation existante dans la base de donnees
	 * 
	 * @param reservation
	 *                   La reservation a enregistrer dans la DB
	 * @return boolean
	 *                   Indique si la reservation a bien ete enregistree
	 * @throws SQLException
	 *                   Exception SQL levee
	 */
	public boolean reserverArticle(Reservation reservation)
	                        throws SQLException {

		DatabaseQuery data = new DatabaseQuery();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rset = null;

		try {
			conn = data.getDBConnection();
			stmt = data.getNewStatement();
			stmt.execute("INSERT INTO Reservations VALUES ("
					+ reservation.getCodeReservation() + ", "
					+ reservation.getCodeArticle() + ", "
					+ reservation.getCodeClient() + ", "
					+ reservation.getQuantite() + ", "
					+ reservation.estPayee() + ";");

			rset = stmt.getResultSet();
			// reservation fournisseur
			// reservation transport

		} catch (SQLException sqlex) {
			System.out.println("reserverArticle: Exception SQL: "
			                  + sqlex.toString());
			return false;
		} finally {
			DatabaseQuery.free(rset, stmt, conn);
		}

		return true; //return boolean transport et boolean query
	}
	
	/**
	 * 
	 * @param reservation
	 *                   La reservation a annuler
	 * @return boolean
	 *                   Indique si la reservation a bien ete annulee
	 * @throws SQLException
	 *                   Exception SQL levee
	 */
	boolean annulerReservation(Reservation reservation) throws SQLException {
		DatabaseQuery data = new DatabaseQuery();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rset = null;

		try {
			conn = data.getDBConnection();
			stmt = data.getNewStatement();
			stmt.execute("DELETE FROM Reservations WHERE codeReservation = " + reservation.getCodeReservation() + "AND codeArticle = "
						+ reservation.getCodeArticle() + "AND codeClient = " + reservation.getCodeClient() + "AND quantite = "
						+ reservation.getQuantite() + "AND booleanPaiementEffectue = " + reservation.estPayee() + ";");

			rset = stmt.getResultSet();
			// Suppr reserv four
			// suppr reserv trans
		} catch (SQLException sqlex) {
			System.out.println("annulerReservation: Exception SQL: "
			                  + sqlex.toString());
			return false;
		}
		
		DatabaseQuery.free(rset, stmt, conn);
		return true;
	}
}
